from Inflator import Inflator
import numpy as np
import sys
import pandas as p
import matplotlib.pyplot as plt
from sfi import Scalar, Frame, Data
import scipy.stats as stat
from collections import OrderedDict
import os
def importData(frame, cols):
    #Load data from stata
    data = p.DataFrame(np.array(frame.get(cols, missingval=np.nan)), columns=cols)
    #Sort data
    data = data.sort_values('state')

    return data

#Data columns imported from Stata
cols = ['state', 'year', 'policyRegVMT', 'noPolicyRegVMT', 'deltaRegVMT', 'policyRegGasUse', 'noPolicyRegGasUse', 'deltaRegGasUse', 'policyVMT', 'noPolicyVMT', 'deltaVMT', 'policyGasUse', 'noPolicyGasUse', 'deltaGasUse', 'vmtPerVehAdded', 'gasUsePerVehAdded', 'stateYearsVMT', 'stateYearsGasUse']

#Save file for estimated externalities
fileName = 'PythonScripts/Tables/externalitiesFull.xlsx'

if(not sys.platform=='darwin'):
    fileName = 'PythonScripts\Tables\externalitiesFull.xlsx'

#Create writer object
writer = p.ExcelWriter(fileName)

#From Parry, Walls, Harrington (2007)
baseYear = 2005 #Unclear but they seem to update some dollar amounts to 2005$, which was also used in Delucchi, M. A., & McCubbin, D. R. (2010) p.8.
refYear = 2021
#Create instance of Inflator object
infl = Inflator()

#Get the correct inflation factors
gdpInflationMultiplier = infl.inflateGDP(1, baseYear, refYear)
cpiInflationMultiplier = infl.inflateAll(1, baseYear, refYear)

#Table 2 from Parry, Walls, Harrington (2007)
mileageCost = .10*cpiInflationMultiplier #dollars/mile
fuelCosts = .18*gdpInflationMultiplier #dollars/gallon

#Iterate through all null hypotheses to calculate externalities
for i in range(1,10):

    print("Working on externality analysis {}".format(i))
    #Establish connect to externalities frame
    tempFrame = Frame.connect("externalities{}".format(i))
    #Import data for the analysis
    initData = importData(tempFrame, cols)


    #Ensure years are numeric
    years = list(initData['year'].astype(float))

    #Store inflation factors
    initData['GDP Inflation Factor'] = gdpInflationMultiplier
    initData['CPI Inflation Factor'] = cpiInflationMultiplier
    #Calculate mean externality values
    initData['VMT Externalities ({}$M)'.format(refYear)] = (initData['deltaVMT'].astype(float) * mileageCost/1000000)/initData.loc[:, 'stateYearsVMT'].astype(float)
    initData['Gas Use Externalities ({}$M)'.format(refYear)] = (initData['deltaGasUse'].astype(float) * fuelCosts/1000000)/initData.loc[:, 'stateYearsGasUse'].astype(float)
    initData['Total Externalities ({}$M)'.format(refYear)] = initData['VMT Externalities ({}$M)'.format(refYear)] + initData['Gas Use Externalities ({}$M)'.format(refYear)]

    #Calculate mean policy impacts on travel and gas use
    initData['deltaVMT'.format(refYear)] = (initData['deltaVMT'].astype(
        float)) / initData.loc[:, 'stateYearsVMT'].astype(float)
    initData['deltaGasUse'.format(refYear)] = (initData['deltaGasUse'].astype(
        float)) / initData.loc[:, 'stateYearsGasUse'].astype(float)

    #Cast all policy impacts to numeric types
    initData['deltaRegVMT'] = initData['deltaRegVMT'].astype(float)
    initData['deltaRegGasUse'] = initData['deltaRegGasUse'].astype(float)
    initData['deltaVMT'] = initData['deltaVMT'].astype(float)
    initData['deltaGasUse'] = initData['deltaGasUse'].astype(float)

    #rename columns
    initData = initData.rename({'year': "Year", 'state': 'State', 'deltaRegVMT': "Policy Impact on Registrations (VMT Model)", 'deltaRegGasUse': "Policy Impact on Registrations (Gas Use Model)", 'deltaVMT': "Policy Impact on VMT", 'deltaGasUse': "Policy Impact on Gas Use"}, axis="columns")

    #Write to excel sheet
    initData.to_excel(writer, "FullExternalities{}".format(i), index=False)
    #Drop year classification
    initData = initData.drop('Year', axis='columns')
    #Sum along states
    initData = initData.groupby('State', as_index=False).sum()
    #Make a copy of summed data
    totalData = initData.copy()
    #Sum across all states
    totalData['State'] = '\\textbf{Total}'
    totalData = totalData.groupby('State', as_index=False).sum()
    #append total to dataframe
    initData = initData.append(totalData)
    #save summary tables
    initData.to_excel(writer, "Externalities{}".format(i), index=False)

#Close excel writer object
writer.close()